const express = require('express');
const router = express.Router();
const { getKnex } = require('../db/knex');
const Result = require('../utils/Result');

// 查询平台类型列表
router.get('/platforms', async (req, res) => {
  try {
    const knex = getKnex();
    const list = await knex('platform').select('*').orderBy('id');
    res.json(Result.success(list));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('查询技术栈失败'));
  }
});

// 查询所有作品（支持分页、筛选）
router.get('/products', async (req, res) => {
  try {
    const knex = getKnex();
    const { page = 1, pageSize = 10, category, techStack } = req.query;

    // ✅ 先查 total，只查 projects
    const totalQuery = knex('projects').count({ count: '*' });

    if (category) {
      totalQuery.where('category_id', category);
    }

    if (techStack) {
      totalQuery.whereIn('id', function () {
        this.select('project_id')
          .from('project_technologies')
          .where('technology_id', techStack);
      });
    }

    const totalObj = await totalQuery.first();
    const total = totalObj?.count || 0;

    // ✅ 再查数据，使用子查询聚合 tech_stack_ids 和 images
    let listQuery = knex('projects').select(
      'projects.*',
      knex.raw(`(
        select JSON_ARRAYAGG(technology_id)
        from project_technologies
        where project_id = projects.id
      ) as tech_stack_ids`),
      knex.raw(`(
        select JSON_ARRAYAGG(image_url)
        from project_images
        where project_id = projects.id
      ) as images`),
      knex.raw(`(
        select JSON_ARRAYAGG(technology_id)
        from project_platforms
        where project_id = projects.id
      ) as platform_ids`)
    );

    if (category) {
      listQuery.where('projects.category_id', category);
    }

    if (techStack) {
      listQuery.whereIn('projects.id', function () {
        this.select('project_id')
          .from('project_technologies')
          .where('technology_id', techStack);
      });
    }

    const list = await listQuery
      .orderBy('projects.created_at', 'desc')
      .offset((page - 1) * pageSize)
      .limit(Number(pageSize));

    res.json(Result.success({ total, list }));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('查询作品失败'));
  }
});

// 查询技术栈列表
router.get('/tech-stacks', async (req, res) => {
  try {
    const knex = getKnex();
    const list = await knex('technologies').select('*').orderBy('id');
    res.json(Result.success(list));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('查询技术栈失败'));
  }
});

// 查询分类列表
router.get('/categories', async (req, res) => {
  try {
    const knex = getKnex();
    const categories = await knex('categories').select('*').orderBy('id');
    res.json(Result.success(categories));
  } catch (error) {
    console.error(error);
    res.json(Result.fail('查询分类失败'));
  }
});

module.exports = router;